﻿
using System;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MockPrjTeam1.Entity;
using System.Data;
using System.Data.SqlClient;
using SD.Commons.Helpers;

namespace MockPrjTeam1.DAO
{
    public class ProgrammeDAO
    {
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter da;

        public ProgrammeDAO()
        {
            conn = new SqlConnection(SqlHelper.ConnectionString);
        }

        public List<Programme> GetProgramme()
        {

            this.mintTotalRecords = GetCount();
            this.mintPageCount = this.mintTotalRecords / this.mintPageSize;
            if (this.mintTotalRecords % this.mintPageSize > 0)
                this.mintPageCount++;
            return LoadPage();
        }

        public bool Btn_Save_Programme(Programme programme, string editOradd)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            try
            {
                DataTable dt = new DataTable();
                cmd = new SqlCommand("btn_save_programme", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@programmeName", SqlDbType.VarChar).Value = programme.ProgrammeName;
                cmd.Parameters.Add("@description", SqlDbType.VarChar).Value = programme.Description;
                cmd.Parameters.Add("@contactID", SqlDbType.Int).Value = 1;
                cmd.Parameters.Add("@contactName", SqlDbType.VarChar).Value = programme.ContactName;
                cmd.Parameters.Add("@active", SqlDbType.Bit).Value = programme.Active;
                cmd.Parameters.Add("@editOradd", SqlDbType.VarChar).Value = editOradd;
                if (editOradd == "A")
                {
                    cmd.Parameters.Add("@programmeID", SqlDbType.Int).Value = 10;
                }
                else if (editOradd == "E")
                {
                    cmd.Parameters.Add("@programmeID", SqlDbType.Int).Value = programme.ProgrammeID;
                }

                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                if (dt.Rows[0][0].ToString() == "OK")
                {
                    return true;
                }
                else
                    return false;
            }
            catch
            {
                return false;
            }
            return false;
        }


        private int GetCount()
        {
            int intCount = 0;
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                string strSql = "";
                strSql = "SELECT Rows FROM SYSINDEXES WHERE Id = OBJECT_ID('Programme') AND IndId < 2";

                cmd = this.conn.CreateCommand();
                cmd.CommandText = strSql;
                intCount = (int)cmd.ExecuteScalar();
                cmd.Dispose();

            }
            catch
            {

            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return intCount;
        }

        private int mintTotalRecords;
        private int mintPageSize = 8;
        private int mintPageCount;
        private int mintCurrentPage = 0;

        private List<Programme> LoadPage()
        {
            DataTable dt = new DataTable();
            string strSql = "";
            int intSkip = (this.mintCurrentPage * this.mintPageSize);

            strSql = "SELECT TOP " + this.mintPageSize +
                " ProgrammeID,ProgrammeName,Description,ContactID,ContactName, Active from Programme WHERE ProgrammeID NOT IN " +
                "(SELECT TOP " + intSkip + " ProgrammeID FROM Programme) and Active = 1";
            cmd = this.conn.CreateCommand();
            cmd.CommandText = strSql;
            da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            List<Programme> listpro = new List<Programme>();
            foreach (DataRow r in dt.Rows)
            {
                Programme p = new Programme();
                p.ProgrammeID = (int)r[0];
                p.ProgrammeName = r[1].ToString();
                p.Description = r[2].ToString();
                p.ContactID = (int)r[3];
                p.ContactName = r[4].ToString();
                p.Active = (bool)r[5];
                listpro.Add(p);
            }
            return listpro;
        }

        public List<Programme> NextPage()
        {
            this.mintCurrentPage++;
            if (this.mintCurrentPage > (this.mintPageCount - 1))
                this.mintCurrentPage = this.mintPageCount - 1;
            return LoadPage();
        }

        public List<Programme> PreviousPage()
        {
            if (this.mintCurrentPage == this.mintPageCount)
                this.mintCurrentPage = this.mintPageCount - 1;

            this.mintCurrentPage--;

            if (this.mintCurrentPage < 1)
                this.mintCurrentPage = 0;

            return LoadPage();
        }

        public Programme GetOne(int id)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            try
            {
                DataTable dt = new DataTable();
                cmd = new SqlCommand("GetOneProgramme", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@p1", SqlDbType.Int).Value = id;
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                Programme p = new Programme();
                p.ProgrammeID = (int)dt.Rows[0][0];
                p.ProgrammeName = dt.Rows[0][1].ToString();
                p.Description = dt.Rows[0][2].ToString();
                p.ContactID = (int)dt.Rows[0][3];
                p.ContactName = dt.Rows[0][4].ToString();
                p.Active = (bool)dt.Rows[0][5];
                return p;
            }
            catch
            {

            }
            return null;
        }
    }
}